#include "dictdbutils.h"

DictDBUtils::DictDBUtils(const wxString& dictdb_path) {
    try {
        _dictdb.Open(dictdb_path);
        // _dictdb.Begin(WXSQLITE_TRANSACTION_DEFAULT);

    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
    }
}

int DictDBUtils::rm_indexes() {
    try {
        _dictdb.ExecuteUpdate(wxT("DROP INDEX IF EXISTS w_index_l_word;"
                ));

        _dictdb.ExecuteUpdate(wxT("DROP INDEX IF EXISTS w_index_o_word;"
                ));

        _dictdb.ExecuteUpdate(wxT("DROP INDEX IF EXISTS e_index_w_id;"
                ));

        _dictdb.ExecuteUpdate(wxT("UPDATE words SET w_l_word=NULL;"
                ));
        
        _dictdb.ExecuteUpdate(wxT("VACUUM;")); 
    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
        return -1;
    }
    
    return 0;
}

int DictDBUtils::mk_indexes() {
    try {
        _dictdb.ExecuteUpdate(wxT("CREATE INDEX IF NOT EXISTS w_index_o_word \
                            ON words ( \
                                w_o_word ASC \
                            );"
                ));

        _dictdb.ExecuteUpdate(wxT("CREATE INDEX IF NOT EXISTS w_index_l_word \
                            ON words ( \
                                w_l_word ASC \
                            );"
                ));

        _dictdb.ExecuteUpdate(wxT("CREATE INDEX IF NOT EXISTS e_index_w_id \
                            ON explanations ( \
                                w_id ASC \
                            );"
                ));
        
        _dictdb.ExecuteUpdate(wxT("UPDATE words SET w_l_word=lower(w_o_word);"
                ));
        
        _dictdb.ExecuteUpdate(wxT("VACUUM;")); 
    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
        return -1;
    }
    
    return 0;
}

int DictDBUtils::mk_tables() {
    try {
        // dicts table
        _dictdb.ExecuteUpdate(_T("CREATE TABLE IF NOT EXISTS dicts( \
                            d_id INTEGER PRIMARY KEY, \
                            d_name NOT NULL, \
                            d_author, \
                            d_email, \
                            d_website, \
                            d_version, \
                            d_license, \
                            d_description, \
                            d_origlang, \
                            d_destlang \
                          );"
                ));

        // words table
        _dictdb.ExecuteUpdate(wxT("CREATE TABLE IF NOT EXISTS words ( \
                            w_id INTEGER PRIMARY KEY, \
                            w_o_word NOT NULL,\
                            w_l_word \
                          );"
                ));

        // explanations table
        _dictdb.ExecuteUpdate(wxT("CREATE TABLE IF NOT EXISTS explanations ( \
                            w_id  NOT NULL, \
                            d_id  NOT NULL, \
                            e_expl  NOT NULL \
                          );"
                ));

        // resource table
        _dictdb.ExecuteUpdate(wxT("CREATE TABLE IF NOT EXISTS res ( \
                            r_id  INTEGER PRIMARY KEY, \
                            r_data  NOT NULL \
                          );"
                ));
    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
        return -1;
    }
    
    return 0;
}

// -1 -- dict with same name exist, -2, error, >=0 -- success
/*const wxString& d_name, const wxString& d_author,
const wxString& d_email, const wxString& d_website,
const wxString& d_version, const wxString& d_license,
const wxString& d_description, const wxString& d_origlang,
const wxString& d_destlang) {
    
}*/
int DictDBUtils::add_dict_info(const DictInfo& dictinfo) {
    DictInfo info = dictinfo;
    
    info.d_name.Replace(_T("\'"), _T("\'\'"));
    info.d_author.Replace(_T("\'"), _T("\'\'"));
    info.d_email.Replace(_T("\'"), _T("\'\'"));
    info.d_website.Replace(_T("\'"), _T("\'\'"));
    info.d_version.Replace(_T("\'"), _T("\'\'"));
    info.d_license.Replace(_T("\'"), _T("\'\'"));
    info.d_description.Replace(_T("\'"), _T("\'\'"));
    info.d_origlang.Replace(_T("\'"), _T("\'\'"));
    info.d_destlang.Replace(_T("\'"), _T("\'\'"));
    
    try {
        wxString install_sql;

        install_sql.Printf(_T("SELECT d_id FROM dicts \
                    WHERE d_name=\'%s\';"), info.d_name.c_str());

        wxSQLite3ResultSet dicts_ret = _dictdb.ExecuteQuery(install_sql);

        if (dicts_ret.NextRow()) {
            return -1; // dict with same name exist
        }

        // insert words which does not exist
        install_sql.Printf(_T(" \
                INSERT INTO dicts ( \
                        d_name, \
                        d_author, \
                        d_email, \
                        d_website, \
                        d_version, \
                        d_license, \
                        d_description, \
                        d_origlang, \
                        d_destlang \
                    ) \
                    VALUES ( \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\', \
                        \'%s\' \
                    ); \
                "), info.d_name.c_str(), info.d_author.c_str(), info.d_email.c_str(),
            info.d_website.c_str(), info.d_version.c_str(),
            info.d_license.c_str(), info.d_description.c_str(), info.d_origlang.c_str(),
            info.d_destlang.c_str()
        );

        //wxPrintf(_T("insert begin \n"));

        _dictdb.ExecuteUpdate(install_sql);
        //wxPrintf(_T("insert end \n"));

        install_sql.Printf(_T("SELECT d_id FROM dicts \
                    WHERE d_name=\'%s\';"), info.d_name.c_str());

        dicts_ret = _dictdb.ExecuteQuery(install_sql);

        dicts_ret.NextRow();

        return dicts_ret.GetInt(0,-2);
    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
        return -2;
    }
    
    return -2; // never reached here
}

bool DictDBUtils::add_word_to_dict(int d_id, const wxString& w_word,
        const wxString& e_expl) {
    wxString o_word = w_word; // o_word, original word;
    // l_word, converts all characters to lower case 
    wxString expl = e_expl;
    
    o_word.Replace(_T("\'"), _T("\'\'"));
    expl.Replace(_T("\'"), _T("\'\'"));
    
    try {
        wxString install_sql;

        // whether the word already exist
        int w_id = -1;
        
        install_sql.Printf(_T("SELECT w_id FROM words \
                WHERE w_o_word=\'%s\';"), o_word.c_str());

        wxSQLite3ResultSet dicts_ret = _dictdb.ExecuteQuery(install_sql);
        if (dicts_ret.NextRow()) { // word already exist
            w_id = dicts_ret.GetInt(0,-1);
        } else { // insert word which does not exist
            install_sql.Printf(_T(" \
                    INSERT INTO words (w_o_word, w_l_word) \
                        VALUES (\'%s\', \'%s\'); \
                    "), o_word.c_str(), o_word.Lower().c_str()
            );
            _dictdb.ExecuteUpdate(install_sql);

            // get w_id
            install_sql.Printf(_T("SELECT w_id FROM words \
                        WHERE w_o_word=\'%s\';"), o_word.c_str());

            dicts_ret = _dictdb.ExecuteQuery(install_sql);
            dicts_ret.NextRow();
            w_id = dicts_ret.GetInt(0,-1);
        }

        if (w_id<0) {
            return false;
        }

        // insert expl
        install_sql.Printf(_T(" \
                INSERT INTO explanations (w_id, d_id, e_expl) \
                    VALUES(%d, %d, \'%s\'); \
                "), w_id, d_id, expl.c_str()
        );

        _dictdb.ExecuteUpdate(install_sql);
    } catch (wxSQLite3Exception& e) {
        wxLogError(_T("%s: %s, %d"), __DAO_FUNC_NAME__.c_str(), e.GetMessage().c_str(), e.GetErrorCode());
        return false;
    }
    
    return true;
}

DictDBUtils::~DictDBUtils() {
    // _dictdb.Commit();
}
